dbt の generic test にて、where 句の条件を柔軟に管理したい

#dbt

川田

2024.10.17

こんにちは、川田です。

今回試したこと

以前、dbt の generic test で where 句を利用する方法を紹介しました。

dbt の generic test にて、テスト対象のレコードを絞る方法

今回は、where 句に複数の条件を設定したい場合、その条件を柔軟に管理する方法がないか考えてみました。以下のようなイメージです。

image-conditions_

以下 2 種類の条件をどのように管理させるか、という話です。

テストには共通の条件Aが存在する
テスト毎に異なる条件Bが存在する

yq コマンドや専用プログラムを作成して、dbt test の where プロパティを更新すれば良いじゃないか、という方法は今回検討せず、違う方法がないか考えています。

環境

dbt-core
- 1.8.7

動作確認用の DWH として、BigQuery を利用しています。

結論

default の generic test を模した custom generic test を作成して、その custom generic test にて 条件A を管理します。

イメージです。

{% test not_null_custom(model, column_name) %}

{% set column_list = '*' if should_store_failures() else column_name %}

select {{ column_list }}
from {{ model }}
where
    {{ column_name }} is null
    and created_at between '2025-01-01' and '2025-12-01' -- ここが条件Aの部分

{% endtest %}

Writing custom generic data tests

default の generic test のテンプレートファイルは下記にあるため、その内容を参考に 条件A を追加した custom generic test を作成しています。

条件B にあたる部分は、yaml ファイルの where プロパティにてテスト個別で管理を行います。

動作確認

以下の手順で動作を確認します。

1. dbt seed を利用して、テストに利用するデータを BigQuery にロード
1. custom generic test を作成
- なお今回は、custom generic test 内で target の値を利用して、target 毎に where 句を分岐させる方法も併せて試しています
1. dbt test を実行

1. dbt seed を利用して、テストに利用するデータを BigQuery にロード

dbt seed で利用する csv ファイルを、以下の通り作成します。

DBT_PROJECT_ROOT/seeds/data.csv

created_at,name,region,prefecture,status
2024-01-01,AAA,kanto,tokyo,pre
2024-02-01,BBB,kanto,,released
2024-03-01,CCC,kanto,chiba,closed
2024-04-01,DDD,kanto,kanagawa,pre
2024-05-01,EEE,kanto,gunma,released
2024-06-01,FFF,kanto,tochigi,closed
2024-07-01,GGG,kanto,ibaragi,pre
2024-08-01,HHH,chubu,toyama,released
2024-09-01,III,chubu,ishikawa,closed
2024-10-01,JJJ,chubu,,pre
2024-11-01,KKK,chubu,nagano,released
2024-12-01,LLL,chubu,gifu,closed
2025-01-01,MMM,chubu,shizuoka,pre
2025-02-01,NNN,chubu,aichi,released

上記 seed に合致する properties yaml ファイルも、下記の通り用意します。

DBT_PROJECT_ROOT/seeds/data.yml

version: 2

seeds:
  - name: data
    config:
      enabled: true
      tags: ["data"]
      schema: check
    columns:
      - name: created_at
      - name: name
      - name: region
      - name: prefecture
      - name: status

スキーマ名を簡潔にしたいので、generate_schema_name マクロも配置しておきます。（ここは今回の動作検証内容とは関係のない範囲です。）

DBT_PROJECT_ROOT/macro/generate_schema_name.sql

{% macro generate_schema_name(custom_schema_name, node) -%}
    {#
        How does dbt generate a model schema name?
        https://docs.getdbt.com/docs/build/custom-schemas#how-does-dbt-generate-a-models-schema-name
    #}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- else -%}
        {%- if target.name == 'prod' -%}
            {{ custom_schema_name | trim }}
        {%- elif target.name == 'stg' -%}
            stg_{{ custom_schema_name | trim }}
        {%- endif -%}
    {%- endif -%}
{%- endmacro %}

target prod 向けに、dbt seed を実行してみます。

dbt seed --target prod --select ./seeds/data.csv

下記のように、check.data テーブルが作成され、データが登録されました。

bq-table-data_

2. custom generic test を作成

default の not_null generic test に変更を加えて、 custom generic test を作成します。

default の not_null テストのテンプレートを参考に、custom generic test 向けに修正
- Writing custom generic data tests
- default の not_null テストのテンプレート
条件Aにあたる where 句に追加
- created_at カラムの値が、2024-01-01 から 2024-12-01 期間のもの
target の値により、分岐されて where 句に条件が追加されるよう記述
- target が prod の場合、status カラムの値が released のもの
- target が stg の場合、status カラムの値が released または pre のもの

下記が、実際に作成した custom generic test です。

DBT_PROJECT_ROOT/tests/generic/not_null_custom.sql

{% test not_null_custom(model, column_name) %}

{% set column_list = '*' if should_store_failures() else column_name %}

select {{ column_list }}
from {{ model }}
where
    {{ column_name }} is null
    and created_at between '2024-01-01' and '2024-12-01'  -- ここが条件Aにあたる部分
    {%- if target.name == "prod" %} -- ここから下がtargetの値により分岐させている部分
    and status in ('released')
    {%- elif target.name == "stg" %}
    and status in ('released', 'pre')
    {%- endif -%}

{% endtest %}

作成した custom generic test を利用するように、dbt seed 向けの properties ファイルに data_tests プロパティを追加します。

DBT_PROJECT_ROOT/seeds/data.yml

version: 2

seeds:
  - name: data
    config:
      enabled: true
      tags: ["data"]
      schema: check
    columns:
      - name: created_at
      - name: name
      - name: region
      - name: prefecture
        data_tests:
          - not_null_custom:
              name: not_null_custom_data_prefecture_1
              config:
                enabled: true
                tags: ["test_data"]
                where: region in ('kanto')
          - not_null_custom:
              name: not_null_custom_data_prefecture_2
              config:
                enabled: true
                tags: ["test_data"]
                where: region in ('chubu')
      - name: status

3. dbt test を実行

prod と stg を target に指定して、dbt build コマンド(seed + test)を実行してみます。

今回の dbt test で付与される where 句の条件は、以下のパターンとなる筈です。

No.	dbt test 名	対象 target 名	yml ファイルの where プロパティで指定の条件 (`条件B`)	custom generic test に記述した共通条件 (`条件A`)	custom generic test 内で target プロパティの値により分岐される条件
1	not_null_custom_data_prefecture_1	prod	region in ('kanto')	created_at between '2024-01-01' and '2024-12-01'	status in ('released')
2	not_null_custom_data_prefecture_2	prod	region in ('chubu')	created_at between '2024-01-01' and '2024-12-01'	status in ('released')
3	not_null_custom_data_prefecture_1	stg	region in ('kanto')	created_at between '2024-01-01' and '2024-12-01'	status in ('released', 'pre')
4	not_null_custom_data_prefecture_2	stg	region in ('chubu')	created_at between '2024-01-01' and '2024-12-01'	status in ('released', 'pre')

prod 向けに実行

まず、target prod 向けに実行してみます。custom generic test でも store-failures テーブルを作成してくれるか確認するため、--store-failures オプションを付与して実行しています。

$ dbt build --target prod --store-failures --select ./seeds/data.csv
08:48:29  Running with dbt=1.8.7
08:48:30  Registered adapter: bigquery=1.8.2
08:48:30  Unable to do partial parsing because config vars, config profile, or config target have changed
08:48:30  Unable to do partial parsing because profile has changed
08:48:31  Found 2 seeds, 4 data tests, 481 macros
08:48:31
08:48:33  Concurrency: 1 threads (target='prod')
08:48:33
08:48:33  1 of 3 START seed file check.data .............................................. [RUN]
08:48:38  1 of 3 OK loaded seed file check.data .......................................... [INSERT 14 in 4.60s]
08:48:38  2 of 3 START test not_null_custom_data_prefecture_1 ............................ [RUN]
08:48:41  2 of 3 FAIL 1 not_null_custom_data_prefecture_1 ................................ [FAIL 1 in 3.07s]
08:48:41  3 of 3 START test not_null_custom_data_prefecture_2 ............................ [RUN]
08:48:44  3 of 3 PASS not_null_custom_data_prefecture_2 .................................. [PASS in 3.26s]
08:48:44
08:48:44  Finished running 1 seed, 2 data tests in 0 hours 0 minutes and 13.09 seconds (13.09s).
08:48:44
08:48:44  Completed with 1 error and 0 warnings:
08:48:44
08:48:44  Failure in test not_null_custom_data_prefecture_1 (seeds/data.yml)
08:48:44    Got 1 result, configured to fail if != 0
08:48:44
08:48:44    compiled code at target/compiled/dbt_bq_sample/seeds/data.yml/not_null_custom_data_prefecture_1.sql
08:48:44
08:48:44    See test failures:
  ------------------------------------------------------------------------------------
  select * from `xxxxxxxx`.`dbt_test__audit`.`not_null_custom_data_prefecture_1`
  ------------------------------------------------------------------------------------
08:48:44
08:48:44  Done. PASS=2 WARN=0 ERROR=1 SKIP=0 TOTAL=3

コンパイルされ実際に実行されている sql ファイルと、store-failures テーブル内を、それぞれ確認してみます。

No.1 テスト: not_null_custom_data_prefecture_1, target: prod

DBT_PROJECT_ROOT/target/compiled/dbt_bq_sample/seeds/data.yml/not_null_custom_data_prefecture_1.sql

select *
from (select * from `xxxxxxxx`.`check`.`data` where region in ('kanto')) dbt_subquery
where
    prefecture is null
    and created_at between '2024-01-01' and '2024-12-01'
    and status in ('released')

store-failures テーブル。

$ dbt show --target prod --inline 'select * from `xxxxxxxx`.`dbt_test__audit`.`not_null_custom_data_prefecture_1`'
08:59:02  Running with dbt=1.8.7
08:59:03  Registered adapter: bigquery=1.8.2
08:59:03  Unable to do partial parsing because config vars, config profile, or config target have changed
08:59:03  Unable to do partial parsing because profile has changed
08:59:04  Found 2 seeds, 4 data tests, 1 sql operation, 481 macros
08:59:04
08:59:05  Concurrency: 1 threads (target='prod')
08:59:05
08:59:07  Previewing inline node:
| created_at | name | region | prefecture | status   |
| ---------- | ---- | ------ | ---------- | -------- |
| 2024-02-01 | BBB  | kanto  |            | released |

No.2 テスト: not_null_custom_data_prefecture_2, target: prod

DBT_PROJECT_ROOT/target/compiled/dbt_bq_sample/seeds/data.yml/not_null_custom_data_prefecture_2.sql

select *
from (select * from `xxxxxxxx`.`check`.`data` where region in ('chubu')) dbt_subquery
where
    prefecture is null
    and created_at between '2024-01-01' and '2024-12-01'
    and status in ('released')

stg 向けに実行

続いて、target stg 向けに実行してみます。

$ dbt build --target stg --store-failures --select ./seeds/data.csv
09:00:16  Running with dbt=1.8.7
09:00:17  Registered adapter: bigquery=1.8.2
09:00:17  Unable to do partial parsing because config vars, config profile, or config target have changed
09:00:17  Unable to do partial parsing because profile has changed
09:00:18  Found 2 seeds, 4 data tests, 481 macros
09:00:18
09:00:21  Concurrency: 1 threads (target='stg')
09:00:21
09:00:21  1 of 3 START seed file stg_check.data .......................................... [RUN]
09:00:26  1 of 3 OK loaded seed file stg_check.data ...................................... [INSERT 14 in 5.12s]
09:00:26  2 of 3 START test not_null_custom_data_prefecture_1 ............................ [RUN]
09:00:29  2 of 3 FAIL 1 not_null_custom_data_prefecture_1 ................................ [FAIL 1 in 3.78s]
09:00:29  3 of 3 START test not_null_custom_data_prefecture_2 ............................ [RUN]
09:00:33  3 of 3 FAIL 1 not_null_custom_data_prefecture_2 ................................ [FAIL 1 in 3.14s]
09:00:33
09:00:33  Finished running 1 seed, 2 data tests in 0 hours 0 minutes and 14.70 seconds (14.70s).
09:00:33
09:00:33  Completed with 2 errors and 0 warnings:
09:00:33
09:00:33  Failure in test not_null_custom_data_prefecture_1 (seeds/data.yml)
09:00:33    Got 1 result, configured to fail if != 0
09:00:33
09:00:33    compiled code at target/compiled/dbt_bq_sample/seeds/data.yml/not_null_custom_data_prefecture_1.sql
09:00:33
09:00:33    See test failures:
  ----------------------------------------------------------------------------------------
  select * from `xxxxxxxx`.`stg_dbt_test__audit`.`not_null_custom_data_prefecture_1`
  ----------------------------------------------------------------------------------------
09:00:33
09:00:33  Failure in test not_null_custom_data_prefecture_2 (seeds/data.yml)
09:00:33    Got 1 result, configured to fail if != 0
09:00:33
09:00:33    compiled code at target/compiled/dbt_bq_sample/seeds/data.yml/not_null_custom_data_prefecture_2.sql
09:00:33
09:00:33    See test failures:
  ----------------------------------------------------------------------------------------
  select * from `xxxxxxxx`.`stg_dbt_test__audit`.`not_null_custom_data_prefecture_2`
  ----------------------------------------------------------------------------------------
09:00:33
09:00:33  Done. PASS=1 WARN=0 ERROR=2 SKIP=0 TOTAL=3

コンパイルされた sql ファイルと、store-failures テーブル内を確認してみます。

No.3 テスト: not_null_custom_data_prefecture_1, target: stg

DBT_PROJECT_ROOT/target/compiled/dbt_bq_sample/seeds/data.yml/not_null_custom_data_prefecture_1.sql

select *
from (select * from `xxxxxxxx`.`stg_check`.`data` where region in ('kanto')) dbt_subquery
where
    prefecture is null
    and created_at between '2024-01-01' and '2024-12-01'
    and status in ('released', 'pre')

store-failures テーブル。

$ dbt show --target stg --inline 'select * from `xxxxxxxx`.`stg_dbt_test__audit`.`not_null_custom_data_prefecture_1`'
09:02:46  Running with dbt=1.8.7
09:02:46  Registered adapter: bigquery=1.8.2
09:02:47  Found 2 seeds, 4 data tests, 1 sql operation, 481 macros
09:02:47
09:02:48  Concurrency: 1 threads (target='stg')
09:02:48
09:02:51  Previewing inline node:
| created_at | name | region | prefecture | status   |
| ---------- | ---- | ------ | ---------- | -------- |
| 2024-02-01 | BBB  | kanto  |            | released |

No.4 テスト: not_null_custom_data_prefecture_2, target: stg

DBT_PROJECT_ROOT/target/compiled/dbt_bq_sample/seeds/data.yml/not_null_custom_data_prefecture_2.sql

select *
from (select * from `xxxxxxxx`.`stg_check`.`data` where region in ('chubu')) dbt_subquery
where
    prefecture is null
    and created_at between '2024-01-01' and '2024-12-01'
    and status in ('released', 'pre')

store-failures テーブル。

$ dbt show --target stg --inline 'select * from `xxxxxxxx`.`stg_dbt_test__audit`.`not_null_custom_data_prefecture_2`'
09:04:00  Running with dbt=1.8.7
09:04:00  Registered adapter: bigquery=1.8.2
09:04:01  Found 2 seeds, 4 data tests, 1 sql operation, 481 macros
09:04:01
09:04:02  Concurrency: 1 threads (target='stg')
09:04:02
09:04:04  Previewing inline node:
| created_at | name | region | prefecture | status |
| ---------- | ---- | ------ | ---------- | ------ |
| 2024-10-01 | JJJ  | chubu  |            | pre    |

いずれも、想定の通りの条件が付与されています。

有効な情報となれば幸いです。